package com.minisoft.customer;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.cell.CellUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.NumberToTextConverter;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author JueSu
 * @description: 刷客户证件数据问题
 * @date 2023/12/6 14:38
 */
public class FlushCertData {

    @Data
    static class Cert {
        String id;
        String customer_opportunity_id;
        String cert_type_id;
        String cert_number;

        public Cert(String id, String customer_opportunity_id, String cert_type_id, String cert_number) {
            this.id = id;
            this.customer_opportunity_id = customer_opportunity_id;
            this.cert_type_id = cert_type_id;
            this.cert_number = cert_number;
        }
    }

    public static void main(String[] args) {
        ExcelWriter writer = ExcelUtil.getWriter("D:\\temp\\flushcert2.xlsx");
        List<Cert> certs = extractedCerts(writer);
        Map<String, List<Cert>> certMap = certs.stream().collect(Collectors.groupingBy(Cert::getCustomer_opportunity_id));

        writer.setSheet("Sheet1");
        List<String> mktSqls = new ArrayList<>();
        List<String> centerSqls = new ArrayList<>();
        for (int i = 1; i < writer.getRowCount(); i++) {
            if (i == 10) {
//                break;
            }
            JSONObject row = getRowJson(writer,i);
            processMkt(row,mktSqls);
            processCenter(row,centerSqls,certMap,mktSqls,writer);//新增商机，需要回写Mkt和Excel
        }
        Console.log("营销系统更新脚本：");
        mktSqls.forEach(Console::log);
        Console.log("客户中心更新脚本：");
        centerSqls.forEach(Console::log);
        writer.flush();
        writer.close();
    }

    private static Long number = 17000000000L;
    private static void processCenter(JSONObject row, List<String> centerSqls, Map<String, List<Cert>> certMap, List<String> mktSqls, ExcelWriter writer) {
        //更新MKT_ORDER_CON_CUS，MKT_CUSTOMER_CERT
        String cusId = row.getStr("cus_id");
        String verifyCertNumber = row.getStr("verify_cert_number");
        String customerName = row.getStr("customer_name");
        String certCustomerName = row.getStr("cert_customer_name");
        String customerOpportunityId = row.getStr("customer_opportunity_id");
        String sql;
        //能找到商机，则更新数据，否则新增商机数据
        if (customerName.equals(certCustomerName)) {
            //把对应商机的 customer_cert 证件号码刷对，customer_cert考虑多条数据的情况，保留正确的
            List<Cert> certs = certMap.get(customerOpportunityId);
            if (validateValue(verifyCertNumber)) {
                boolean isFound = false;
                for (int i = 0; i < certs.size(); i++) {
                    Cert cert = certs.get(i);
                    //不相等则处理
                    if (!cert.getCert_number().equals(verifyCertNumber)) {
                        //如果没有找到，且是最后一条记录，则更新
                        if (!isFound && i == certs.size() - 1) {
                            sql = StrUtil.format("update customer_cert set cert_number = '{}' where ID = {};", verifyCertNumber,cert.getId());
                        } else {
                            sql = StrUtil.format("update customer_cert set deleted = 1 where ID = {};", cert.getId());
                        }
                        centerSqls.add(sql);
                    }
                }
            } else {
                //没有证件号码，删除掉现有的
                for (Cert cert : certs) {
                    sql = StrUtil.format("update customer_cert set deleted = 1 where ID = {};", cert.getId());
                    centerSqls.add(sql);
                }
            }
        } else {
            //新增商机相关的数据
            String uuid = IdUtil.fastSimpleUUID();
            String saleTeamId = row.getStr("sale_team_id");
            String saleMainName = row.getStr("sale_main_name");
            String saleMainId = row.getStr("sale_main_id");
            String agentId = row.getStr("agent_id");
            String agentName = row.getStr("agent_name");
            String agentStates = row.getStr("agent_states");
            String contactTypeId = row.getStr("contact_type_id");
            String contactTypeName = row.getStr("contact_type_name");
            String visitFirstDate = DateUtil.formatDateTime(row.getDate("visit_first_date"));
            String visitLastDate = DateUtil.formatDateTime(row.getDate("visit_last_date"));
            String followLastDate = DateUtil.formatDateTime(row.getDate("follow_last_date"));
            String visitChannelId = row.getStr("visit_channel_id");
            String visitChannelName = row.getStr("visit_channel_name");
            String visitCode = row.getStr("visit_code");
            String orgId = row.getStr("org_id");
            String channelId = row.getStr("channel_id");
            String channelName = row.getStr("channel_name");
            //根据上面字段，生成商机的insert 语句
            String customerId = IdUtil.fastSimpleUUID();

            sql = StrUtil.format("INSERT INTO customer (customer_id, customer_name,description,customer_type_id,customer_type_name,create_time,update_time)  VALUES ('{}', '{}','{}', '{}','{}',now(),now());",customerId,certCustomerName,"刷数20231207","1002001","个人客户");
            centerSqls.add(sql);

            sql = StrUtil.format("INSERT INTO customer_opportunity (customer_opportunity_id, customer_id,customer_name,sale_team_id, sale_main_name, sale_main_id, agent_id, agent_name, agent_states, contact_type_id, contact_type_name, visit_first_date, visit_last_date, follow_last_date, visit_channel_id, visit_channel_name, visit_code, org_id, channel_id, channel_name,opportunity_state,create_time,update_time) VALUES ('{}', '{}','{}','{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}',5,now(),now());",
                    uuid, customerId,certCustomerName,saleTeamId, saleMainName, saleMainId, agentId, agentName, agentStates, contactTypeId, contactTypeName, visitFirstDate, visitLastDate, followLastDate, visitChannelId, visitChannelName, visitCode, orgId, channelId, channelName);
            centerSqls.add(sql);

            //如果证件号为空，则不生成
            if (validateValue(verifyCertNumber)) {
                sql = StrUtil.format("INSERT INTO customer_cert (customer_cert_id, description,customer_opportunity_id,cert_type_id,cert_number,cert_type_name,sale_team_id,create_time,update_time)  VALUES ('{}', '{}','{}', '{}','{}', '{}','{}',now(),now());",
                        IdUtil.fastSimpleUUID(), "刷数20231207",uuid, "1023001",verifyCertNumber,"身份证",saleTeamId);
                centerSqls.add(sql);
            }

            String mobile = String.valueOf(number++);
            sql = StrUtil.format("INSERT INTO customer_mobile (customer_opportunity_id,description,mobile,reverse_mobile,sale_team_id,sort,create_time,update_time)  VALUES ('{}', '{}','{}', '{}','{}',0,now(),now());",
                    uuid,"刷数20231207",mobile,StrUtil.reverse(mobile),saleTeamId);
            centerSqls.add(sql);

            //返写MKT_CUSTOMER
            sql = StrUtil.format("update MKT_CUSTOMER set CUSTOMER_OPPORTUNITY_ID = '{}' where ID = '{}';",uuid,cusId);
            mktSqls.add(sql);
            //写入Excel
            writer.writeCellValue(9,row.getInt("row"),uuid);
        }
    }

    private static void processMkt(JSONObject row, List<String> mktSqls) {
        //更新MKT_ORDER_CON_CUS，MKT_CUSTOMER_CERT
        String conCusId = row.getStr("con_cus_id");
        String cusId = row.getStr("cus_id");
        String verifyCertNumber = row.getStr("verify_cert_number");
        String certNumber = row.getStr("cert_number");
        //证件有效则更新，否则删除证件记录
        String sql,sql2;
        if (validateValue(verifyCertNumber)) {
            if (!certNumber.equals(verifyCertNumber)) {
                sql = StrUtil.format("update MKT_ORDER_CON_CUS set DOCUMENT_NUMBER = '{}' where ID = '{}';", verifyCertNumber,conCusId);
                sql2 = StrUtil.format("update MKT_CUSTOMER_CERT set CERT_NUMBER = '{}' where CUSTOMER_ID = '{}';", verifyCertNumber,cusId);
                mktSqls.add(sql);
                mktSqls.add(sql2);
            }
        } else {
            sql = StrUtil.format("update MKT_ORDER_CON_CUS set DOCUMENT_NUMBER = '' where ID = '{}';",conCusId);
            sql2 = StrUtil.format("update MKT_CUSTOMER_CERT set DEL_FLAG = 1 where CUSTOMER_ID = '{}';", cusId);
            mktSqls.add(sql);
            mktSqls.add(sql2);
        }
    }

    private static boolean validateValue(String value) {
        return StrUtil.isNotBlank(value) && !value.equals("#N/A") && !value.equals("(null)");
    }

    public static JSONObject getRowJson(ExcelWriter writer, int i) {
        Row header = writer.getOrCreateRow(0);
        Row row = writer.getOrCreateRow(i);
        JSONObject rowJson = new JSONObject();
        rowJson.set("row",i);
        for (int col = 0; col < header.getLastCellNum(); col++) {
            Cell cell = row.getCell(col);
            Object value = CellUtil.getCellValue(cell);
            //如果是字符串且是 #N/A 或者 (null) ，则设置为空
            if (value instanceof String && (value.equals("#N/A") || value.equals("(null)"))) {
                value = "";
            }
            rowJson.set(header.getCell(col).getStringCellValue(),value);
        }
        return rowJson;
    }

    private static List<Cert> extractedCerts(ExcelWriter writer) {
        //先获取证件信息
        writer.setSheet("Sheet2");
        List<Cert> certs = new ArrayList<>();
        for (int i = 1; i < writer.getRowCount(); i++) {
            int col = 0;
            String id = NumberToTextConverter.toText(writer.getCell(col++, i).getNumericCellValue());
            String customer_opportunity_id = writer.getCell(col++, i).getStringCellValue();
            String cert_type_id = NumberToTextConverter.toText(writer.getCell(col++, i).getNumericCellValue());
            String cert_type_name = writer.getCell(col++, i).getStringCellValue();
            String cert_number = writer.getCell(col++, i).getStringCellValue();
            certs.add(new Cert(id,customer_opportunity_id,cert_type_id,cert_number));
        }
        return certs;
    }

}
